package ibms_rcp.dao;

import ibms_rcp.constants.Constants;
import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.BillLog;
import ibms_rcp.model.PayLog;
import ibms_rcp.model.RechargeLog;
import ibms_rcp.util.SqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

public class PayManager {

	public static void saveAllPayLog() {
		ArrayList<Long> allCompanyMois = SqlUtil.getAllCompanyMoi_id();
		for (Long companymoi : allCompanyMois) {
			saveAutoPayLogbyCompany_moi_id(companymoi);
		}
	}

	// 自动保存那些开通预收费功能的房间的 消费记录
	public static void saveAutoPayLogbyCompany_moi_id(Long companymoi) {
		String companyname = SqlUtil.getCompanyNamebyCompanyMoi_id(companymoi);
		ArrayList<BillLog> billLogs = BillManager
				.getFalseBillLogsbyCompany_moi_id(companymoi);
		System.out.println(billLogs.size());
		for (BillLog billLog : billLogs) {
			// 只有预开通的房间，可以收费记录，其余房间只记录账单，不记录收费。
			if (SqlUtil.isBookFunction(billLog.getRoom_moi_id())) {
				PayLog pLog = new PayLog();
				pLog.setCompany_id(companymoi);
				pLog.setCompany_name(companyname);
				pLog.setBill_id(billLog.getId());
				double balance = SqlUtil.getBalancebyCompanyMoi_id(companymoi)
						- billLog.getBill_point();
				//当余额不足以支付账单时，不消费
				if(balance >= 0 ){
					pLog.setBalance(balance);
					savePayLoginDB(pLog);
				}

			}
		}
	}

	// 手动保存（即点击结算按钮）那些没有开通预收费功能的房间的消费记录，包含那些开通预收费但是还有没到预收费时间点的房间
	public static void saveClickPayLogbyCompany_moi_id(Long companymoi) {
		String companyname = SqlUtil.getCompanyNamebyCompanyMoi_id(companymoi);
		ArrayList<BillLog> billLogs = BillManager
				.getFalseBillLogsbyCompany_moi_id(companymoi);
		System.out.println(billLogs.size());
		for (BillLog billLog : billLogs) {
			PayLog pLog = new PayLog();
			pLog.setCompany_id(companymoi);
			pLog.setCompany_name(companyname);
			pLog.setBill_id(billLog.getId());
			// 对于没有开通预存款的用户来说，余额永远是0；
			if (!SqlUtil.isBookFunction(billLog.getRoom_moi_id())) {
				pLog.setBalance(0);
			}
			// 对于开通预存款的用户，点击结算时，因为前一天的账单数据已经自动存入数据库，
			// 所以，只结算上一个结算点到本次点击结算时的数据.当余额不足以支付账单时，不消费
			else {
				double balance = SqlUtil.getBalancebyCompanyMoi_id(companymoi)
						- billLog.getBill_point();
				if(balance >= 0 ){
					pLog.setBalance(balance);
					savePayLoginDB(pLog);
				}

			}
		}
	}

	public static void savePayLoginDB(PayLog pLog) {
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "insert into m2mnap_estate_deal_log(id,company_moi_id,company_name,bill_id,pay_time,point) "
					+ " values(nextval('m2mnap_estate_deal_log_seq'),?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, pLog.getCompany_id());
			pstmt.setString(2, pLog.getCompany_name());
			pstmt.setLong(3, pLog.getBill_id());
			pstmt.setTimestamp(4, SqlUtil.getTimeStamp());
			pstmt.setDouble(5, pLog.getBalance());

			if (pstmt.executeUpdate() == 1) {
				// 记录消费记录表成功，更新账单表的账单为已支付
				System.out.println("记录消费记录表成功");
				sqlString = "update m2mnap_estate_bill_log set dealflag = ? where id =?";
				pstmt = conn.prepareStatement(sqlString);
				pstmt.setBoolean(1, true);
				pstmt.setLong(2, pLog.getBill_id());
				if (pstmt.executeUpdate() == 1) {
					System.out.println("更新账单记录表成功");
				}
				// 更新业主的余额
				sqlString = "update m2mnap_moi_attribute set new_value = ? where moi_id =? and moc_attribute_code =?";
				pstmt = conn.prepareStatement(sqlString);
				pstmt.setString(1, Double.toString(pLog.getBalance()));
				pstmt.setLong(2, pLog.getCompany_id());
				pstmt.setString(3, "bookSaving");
				if (pstmt.executeUpdate() == 1) {
					System.out.println("更新余额成功");
				}
			} else {
				;
			}
			pstmt.close();
			conn.commit();

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public static ArrayList<PayLog> getPayLogsbyCompany_id(Long company_moi_id,String bill_type,String begin,String end) {
		ArrayList<PayLog> pLogs = new ArrayList<PayLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_deal_log where company_moi_id = ?  and " +
					" pay_time > ? and pay_time < ? and bill_id in "+
					" (select id from m2mnap_estate_bill_log where bill_type = ?) order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, company_moi_id);
			
			if(begin == null){
				pstmt.setTimestamp(2, Timestamp.valueOf("1900-10-10 00:00:00"));
			}else{
				pstmt.setTimestamp(2, Timestamp.valueOf(begin+" 00:00:00"));
			}
			
			if(end == null){
				pstmt.setTimestamp(3, Timestamp.valueOf("2200-10-10 00:00:00"));
			}else{
				pstmt.setTimestamp(3, Timestamp.valueOf(end+" 00:00:00"));
			}
			if(bill_type == null){
				pstmt.setString(4, "%");
			}else{
				pstmt.setString(4, bill_type);
			}
			
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				PayLog pLog = new PayLog();
				pLog.setId(rs.getLong("id"));
				pLog.setCompany_id(rs.getLong("company_moi_id"));
				pLog.setCompany_name(rs.getString("company_name"));
				pLog.setBill_id(rs.getLong("bill_id"));
				pLog.setBalance(rs.getDouble("point"));
				pLog.setPay_time(rs.getTimestamp("pay_time"));

				pLogs.add(pLog);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return pLogs;
	}

	/*public static ArrayList<PayLog> getPayLogsbyCompany_Name(String company_name) {
		ArrayList<PayLog> pLogs = new ArrayList<PayLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_deal_log where company_name = ? order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setString(1, company_name);
			
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				PayLog pLog = new PayLog();
				pLog.setId(rs.getLong("id"));
				pLog.setCompany_id(rs.getLong("company_moi_id"));
				pLog.setCompany_name(rs.getString("company_name"));
				pLog.setBill_id(rs.getLong("bill_id"));
				pLog.setBalance(rs.getDouble("point"));
				pLog.setPay_time(rs.getTimestamp("pay_time"));

				pLogs.add(pLog);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return pLogs;
	}*/
	
	public static ArrayList<PayLog> getPayLogsbyRoom_id(Long room_moi_id,String bill_type,String begin,String end) {
		ArrayList<PayLog> pLogs = new ArrayList<PayLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			
			String sqlString = "select * from m2mnap_estate_deal_log where pay_time > ? and pay_time < ? " +
					"and bill_id in " +
					"(select id from m2mnap_estate_bill_log where room_moi_id = ? and bill_type like ?) order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			if(begin == null){
				pstmt.setTimestamp(1, Timestamp.valueOf("1900-10-10 00:00:00"));
			}else{
				pstmt.setTimestamp(1, Timestamp.valueOf(begin+" 00:00:00"));
			}
			
			if(end == null){
				pstmt.setTimestamp(2, Timestamp.valueOf("2200-10-10 00:00:00"));
			}else{
				pstmt.setTimestamp(2, Timestamp.valueOf(end+" 00:00:00"));
			}
			
			pstmt.setLong(3, room_moi_id);
			if(bill_type == null){
				pstmt.setString(4, "%");
			}else if(bill_type.equals("电费")){
				pstmt.setString(4, "%电费%");
			}else {
				System.out.println("click:"+bill_type);
				pstmt.setString(4, bill_type);
			}
			
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				PayLog pLog = new PayLog();
				pLog.setId(rs.getLong("id"));
				pLog.setCompany_id(rs.getLong("company_moi_id"));
				pLog.setCompany_name(rs.getString("company_name"));
				pLog.setBill_id(rs.getLong("bill_id"));
				pLog.setBalance(rs.getDouble("point"));
				pLog.setPay_time(rs.getTimestamp("pay_time"));

				pLogs.add(pLog);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return pLogs;
	}
	
	public static ArrayList<PayLog> getPayLogsbyCompany_Room(Long company_moi_id,Long room_moi_id,String bill_type,String begin,String end) {
		ArrayList<PayLog> pLogs = new ArrayList<PayLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_deal_log where company_moi_id = ? and pay_time > ? and pay_time < ?" +
					" and bill_id in "+
					" (select id from m2mnap_estate_bill_log where room_moi_id = ? and bill_type = ?) order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, company_moi_id);
			if(begin == null){
				pstmt.setTimestamp(2, Timestamp.valueOf("1900-01-01 00:00:00"));
			}else{
				pstmt.setTimestamp(2, Timestamp.valueOf(begin+" 00:00:00"));
			}
			
			if(end == null){
				pstmt.setTimestamp(3, Timestamp.valueOf("2200-01-01 00:00:00"));
			}else{
				pstmt.setTimestamp(3, Timestamp.valueOf(end+" 00:00:00"));
			}
			pstmt.setLong(4, room_moi_id);

			if(bill_type == null){
				pstmt.setString(5, "%");
			}else{
				pstmt.setString(5, bill_type);
			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				PayLog pLog = new PayLog();
				pLog.setId(rs.getLong("id"));
				pLog.setCompany_id(rs.getLong("company_moi_id"));
				pLog.setCompany_name(rs.getString("company_name"));
				pLog.setBill_id(rs.getLong("bill_id"));
				pLog.setBalance(rs.getDouble("point"));
				pLog.setPay_time(rs.getTimestamp("pay_time"));

				pLogs.add(pLog);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return pLogs;
	}

	public static void main(String[] args) {
		PayManager.saveAllPayLog();
	}

}
